/**
 * Een <code>KasStorage</code> klasse
 * 
 * Klasse om meetgegevens in de de kas-database te schrijven.
 * 
 * @version 0.2
 * @author Diederich Kroeske
 * @since 0.1
 * @parameterlist constructor extended with password field
 */
package mechanics;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;

public class KasStorage {
	private Connection con;

	/**
	 * Constructor.
	 * 
	 * Maakt verbinding met de database
	 * 
	 * @param host database host. student.aii.avans.nl
	 * @param portNumber portNumer is altijd 3306 voor database connectie
	 * @param database database naam
	 * @param user mysql user
	 * @param groepsnummer groepsnummer waaronder de data wordt opgeslagen:
	 *            groepen A t/m G respectievelijk nummers 1 t/m 7
	 */
	public KasStorage(String host, int portNumber, String database, String user, String pw) {
		// Test for mysql JDBC driver
		testDriver();
		con = connectToDatabase(host, portNumber, database, user, pw);
	}

	/**
	 * Slaat gegevens op in de database
	 * 
	 * @param humidity De vochtigheid
	 * @param light Hoeveelheid licht
	 * @param temperature De temperatuur
	 */
	public void storeMessures(int humidity, int light, int temperature) {
		String str = "INSERT INTO meetgegevens VALUES(NULL,'" + getDate() + "', '" + getTime() + "', '" + humidity
				+ "', '" + light + "', '" + temperature + "');";
		storeInDB(str);
	}

	/**
	 * Haalt gegevens op uit de database
	 * 
	 * @param date De datum waarvan we gegevens willen
	 * @return De gevraagde gegevens
	 */
	public ArrayList<Meting> retrieveMessures(String date) {
		String str = "SELECT * FROM meetgegevens WHERE date = '" + date + "';";
		System.out.println(str);
		return (retreiveFromDB(str));
	}

	public String retrieveSetting(String setting) {
		String str = "SELECT * FROM instellingen WHERE parameter = '" + setting + "';";
		System.out.println(str);
		return (retreiveSettingFromDB(str));
	}
	
	public void storeSetting(String parameter, String value) {
		String str = "UPDATE instellingen SET value='" + value + "' WHERE parameter='" + parameter + "';";
		storeInDB(str);
	}

	/**
	 * Gooi alle sensorgegevens weg!! (alleen van jullie groep)
	 */
	public void deleteAll() {
		String str = "truncate table meetgegevens;";
		storeInDB(str);
	}

	private String getDate() {
		Calendar now = Calendar.getInstance();
		SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
		return formatter.format(now.getTime());
	}

	private String getTime() {
		Calendar now = Calendar.getInstance();
		SimpleDateFormat formatter = new SimpleDateFormat("hh:mm:ss");
		return formatter.format(now.getTime());
	}

	/*
	 * ****************************** DATABASE ACCESS ROUTINES
	 * **************************************************
	 */
	/*
	 * De code vanaf hier is niet zo van belang voor de werking van de klasse.
	 */
	private void storeInDB(String query) {
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			System.out.println("SQLExeption");
			e.printStackTrace();
			System.exit(1);
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				System.out.println("SQLExeption");
				System.exit(1);
			}
		}
	}

	protected ArrayList<Meting> retreiveFromDB(String query) {
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Meting> qar = new ArrayList<Meting>();
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				Meting m = new Meting(rs.getString("date"), rs.getString("time"), Integer.valueOf(rs.getString("H")),
						Integer.valueOf(rs.getString("L")), Integer.valueOf(rs.getString("T")));
				qar.add(m);
			}
		} catch (SQLException e) {
			System.out.println("SQLException");
			e.printStackTrace();
			System.exit(1);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}

				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				System.out.println("SQLExeption");
				e.printStackTrace();
				System.exit(1);
			}
		}
		return (qar);
	}

	protected String retreiveSettingFromDB(String query) {
		Statement stmt = null;
		ResultSet rs = null;
		String setting = null;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				setting = rs.getString("value");
			}
		} catch (SQLException e) {
			System.out.println("SQLException");
			e.printStackTrace();
			System.exit(1);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}

				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
				System.out.println("SQLExeption");
				e.printStackTrace();
				System.exit(1);
			}
		}
		return (setting);
	}

	protected Connection connectToDatabase(String host, int portNumber, String database, String user, String passwd) {
		Connection con = null;

		String url = "";
		try {
			url = "jdbc:mysql://" + host + ":" + portNumber + "/" + database + "?user=" + user + "&password=" + passwd;
			con = DriverManager.getConnection(url);
			System.out.println("Connection string: '" + url + "' accepted.");
		} catch (java.sql.SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
			System.exit(1);
		}
		return con;
	}

	protected void testDriver() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception e) {
			System.out.println("Kan MySQL JDBC Driver niet vinden. Bye..");
			System.exit(1);
		}
	}
}